% cohort method to statistics on year data
clear all;
clc;
% freqj=4;% 4 quarters
year_list=1998:2010;

year_list_text=num2str(year_list);
% quarter_list=[1:4]';
% quarter_list_text=num2str(quarter_list);
rateList= 1.5:0.5:4.5;
rateList=[rateList, -2];

db1_open = database('TP06_Y_RJ_fin_R5_G250_1000k_MR7','', ''); %open the connection to the database


pathCountUp = zeros(11,7,8);
pathCountMaintain = zeros(11,7,8);
pathCountDown = zeros(11,7,8);
for yearIdx=1:11 % i=1:11 number of year
    
    year1 = num2str(year_list(yearIdx));
    year2 = num2str(year_list(yearIdx+2));
    year_lookfor=year_list(yearIdx:yearIdx+2);

    %get data for 3 years
    sqlquery1 = strcat('SELECT Year, [Customer Number] as custumerId, [Overall Risk Rating (Hist)] as riskRating, status, default FROM TP06_Y_RJ_fin_R5_G250_1000k_MR7 where TP06_Y_RJ_fin_R5_G250_1000k_MR7.year BETWEEN ''',year1,''' and ''',year2,'''');
    setdbprefs('DataReturnFormat','structure')
    results = fetch(db1_open, sqlquery1);
    
    val = size(results.Year);
    
    custumersMap = containers.Map();
    
    for i =1:val
        
        custumerIdStr = results.custumerId{i};
        riskRating = results.riskRating(i);
        year = str2double(results.Year(i));
        
        
        default = strcmp('Default', results.default(i));
        unhealthy = strcmp('Unhealthy', results.status(i));
        
        if isKey(custumersMap, custumerIdStr) == 0
            %the custumer is not in the map, so create it
            
            %create structure
            myStruct.custumerId = custumerIdStr;
            myStruct.rates = [-1, -1, -1];
            
        else
            myStruct = custumersMap(custumerIdStr);
        end
        % put the right rating
        myStruct = fillRate(myStruct, year_lookfor, year, riskRating, default, unhealthy);
        
        % insert data in the map
        custumersMap(custumerIdStr) = myStruct;
    end
    
    
    
    
    custumersStructures = custumersMap.values;
    custumersStructuresLength = length(custumersStructures);
    
    
    year_lookfor_length = length(year_lookfor);
    
    
    % -1 == disapear
    % -2 == default
    % 0  == i don't know
    for i = 1:custumersStructuresLength
        myStruct = custumersStructures{i};
        
        for j = 1: year_lookfor_length
            rate = myStruct.rates(j);
            
            if rate == -1 || rate == 0
                remove(custumersMap, myStruct.custumerId);
                break;
            elseif (rate == -2) && j ~= year_lookfor_length
                remove(custumersMap, myStruct.custumerId);
                break;
            end
        end
    end
    
    
    custumersStructures = custumersMap.values;
    custumersStructuresLength = length(custumersStructures);
    
    year_lookfor_length = length(year_lookfor);
    
    
    for i = 1 : custumersStructuresLength
        myStruct = custumersStructures{i};
        
        
        position = zeros(1,year_lookfor_length);
        for j = 1: year_lookfor_length
            rate = myStruct.rates(j);
            p = find(rateList == rate);
            position(j) = p;
        end
        
        % for those whose upgrade from t-2 to t-1
        if position(1) < position(2)    
        % get the count for one path
        pCountUp = pathCountUp(yearIdx,position(2),position(3));
        %increment the count + 1 for that path
        pathCountUp(yearIdx,position(2),position(3)) = pCountUp + 1;
        elseif position (1) == position (2)
            pCountMaintain = pathCountMaintain(yearIdx,position(2),position(3));
            pathCountMaintain(yearIdx,position(2),position(3)) = pCountMaintain + 1;
        else
            pCountDown = pathCountDown(yearIdx,position(2),position(3));
            pathCountDown(yearIdx,position(2),position(3)) = pCountDown + 1;
        end
    end
    disp(year);
    %result(yearIdx).year = year;
    %result(yearIdx).pathCount = pathCount;
end


% calculate up-down-maintain Momentum Matrix

up(:,:) = sum(pathCountUp,1);
maintain(:,:) = sum(pathCountMaintain,1);
down(:,:) = sum(pathCountDown,1);

Total(:,:)=up(:,:) + maintain(:,:)+ down(:,:);

% filename='TP06_Y_RJ_fin_R5_G1000k_MR7_MomentumMatrix2.xls';
% sheetlist1=strcat('up');
% sheetlist2=strcat('down');
% sheetlist3=strcat('maintain');
% xlswrite(filename, up, sheetlist1);
% xlswrite(filename, down, sheetlist2);
% xlswrite(filename, maintain, sheetlist3);










% asdf = m.size();


%filter  the data
%   *custumer no default in the 2 first year
%   *custumer is there for the 3 years


% fill the matrix


% take the rating from custumer for year 1 , 2 ,3 and set in the
% matrix














%
%
%
%     for rating=1.5:length(Rate_list); % number of diff rating 0-5
%
%
%         if strcmp(db1_cell,'No Data')
%             disp('check database');
%             year;
%             rating;
%             %iijj
%             %final_result=zeros(j,:)=0;
%         else
%             custom_number_list=cell2mat(db1_cell(:,3));
%             [total_num,col]=size(custom_number_list); % total_num is the number of companies from last year
%             final_result(year,rating,rating,8)=total_num;
%             %final_result(year,iijj,j,13)=total_num;
%             clear curs sqlquery1 db1_cell custom_number_list db1_open;
%
%
%             db1_open = database('TP06_Y_RJ_fin_R5_G1000k_MR7','', ''); %open the connection to the database
%             sqlquery2 = strcat('SELECT [TP06_Y_RJ_fin_R5_G1000k_MR7].[Overall Risk Rating (Hist)] AS Expr1, Count([TP06_Y_RJ_fin_R5_G1000k_MR7].[Customer Number]) AS NumCN FROM TP06_Y_RJ_fin_R5_G1000k_MR7, (SELECT [TP06_Y_RJ_fin_R5_G1000k_MR7].[Customer Number] FROM TP06_Y_RJ_fin_R5_G1000k_MR7 WHERE (((TP06_Y_RJ_fin_R5_G1000k_MR7.Year)=''',year_list_text(year,:),''') And ((TP06_Y_RJ_fin_R5_G1000k_MR7.[Overall Risk Rating (Hist)])=',num2str(Rate_list(rating)),') And ((TP06_Y_RJ_fin_R5_G1000k_MR7.Status) Is Null))) AS q1 WHERE (((TP06_Y_RJ_fin_R5_G1000k_MR7.Year)=''',year_list_text(year+1,:),''') And ((TP06_Y_RJ_fin_R5_G1000k_MR7.Status) Is Null)) And ((TP06_Y_RJ_fin_R5_G1000k_MR7.[Customer Number])=q1.[Customer Number]) GROUP BY [TP06_Y_RJ_fin_R5_G1000k_MR7].[Overall Risk Rating (Hist)]');
%             curs = exec(db1_open, sqlquery2);
%             curs = fetch(curs);
%             db2_cell=curs.Data;
%             close (db1_open);
%             clear curs;
%             stats_list=cell2mat(db2_cell);
%             [mmx,nnx]=size(stats_list);
%             for ii=1:mmx
%                 switch stats_list(ii,1)
%                     case 0
%                         final_result(year,rating,1) = stats_list(ii,2);
%                     case 1
%                         final_result(year,rating,2) = stats_list(ii,2);
%                     case 1.5
%                         final_result(year,rating,3) = stats_list(ii,2);
%                     case 2
%                         final_result(year,rating,4) = stats_list(ii,2);
%                     case 2.5
%                         final_result(year,rating,5) = stats_list(ii,2);
%                     case 3
%                         final_result(year,rating,6) = stats_list(ii,2);
%                     case 3.5
%                         final_result(year,rating,7) = stats_list(ii,2);
%                     case 4
%                         final_result(year,rating,8) = stats_list(ii,2);
%                     case 4.5
%                         final_result(year,rating,9) = stats_list(ii,2);
%                     otherwise
%                         disp('WTF!!!')
%                 end
%             end
%             clear curs sqlquery2 db2_cell stats_list db1_open;
%
%             db1_open = database('TP06_Y_RJ_fin_R5_G1000k_MR7','', ''); %open the connection to the database
%             %sqlquery3 = strcat('SELECT Count(TP06_Y_RJ_fin_R5_G1000k_MR7.[Customer Number]) AS NumCN FROM TP06_Y_RJ_fin_R5_G1000k_MR7, (SELECT [TP06_Y_RJ_fin_R5_G1000k_MR7].[Customer Number] FROM TP06_Y_RJ_fin_R5_G1000k_MR7 WHERE (((TP06_Y_RJ_fin_R5_G1000k_MR7.Year)=''',year_list_text(year,:),''') And ((TP06_Y_RJ_fin_R5_G1000k_MR7.[Overall Risk Rating (Hist)])=',num2str(Rate_list(j)),') And ((TP06_Y_RJ_fin_R5_G1000k_MR7.Status) Is Null)))  AS q1 WHERE (((TP06_Y_RJ_fin_R5_G1000k_MR7.Year)=''',year_list_text(year,:),''') AND ((TP06_Y_RJ_fin_R5_G1000k_MR7.Default)="Default") AND ((TP06_Y_RJ_fin_R5_G1000k_MR7.[Customer Number])=[q1].[Customer Number]))');
%             sqlquery3 = strcat('SELECT Count(TP06_Y_RJ_fin_R5_G1000k_MR7.[Customer Number]) AS NumCN FROM TP06_Y_RJ_fin_R5_G1000k_MR7, (SELECT [TP06_Y_RJ_fin_R5_G1000k_MR7].[Customer Number] FROM TP06_Y_RJ_fin_R5_G1000k_MR7 WHERE (((TP06_Y_RJ_fin_R5_G1000k_MR7.Year)=''',year_list_text(year,:),''') And ((TP06_Y_RJ_fin_R5_G1000k_MR7.[Overall Risk Rating (Hist)])=',num2str(Rate_list(rating)),') And ((TP06_Y_RJ_fin_R5_G1000k_MR7.Status) Is Null)))  AS q1 WHERE (((TP06_Y_RJ_fin_R5_G1000k_MR7.Year)=''',year_list_text(year,:),''') AND ((TP06_Y_RJ_fin_R5_G1000k_MR7.Default)="Default") AND ((TP06_Y_RJ_fin_R5_G1000k_MR7.[Customer Number])=[q1].[Customer Number]))');
%             curs = exec(db1_open, sqlquery3);
%             curs = fetch(curs);
%             db3_cell=curs.Data;
%             close (db1_open);
%             clear curs;
%             if strcmp(db3_cell,'No Data')
%                 final_result(year,rating,11) = 0;
%             else
%                 default_list=cell2mat(db3_cell);
%                 final_result(year,rating,11)= default_list(:,1);
%                 %final_result(year,iijj,j,11) = default_list(ii,2);
%             end
%             clear curs sqlquery3 db3_cell stats_list db1_open;
%
%             final_result(year,rating,12)=final_result(year,rating,13)-sum(final_result(year,rating,1:11));
%         end
%    end

%end

close (db1_open);
